'''-- 专家认证表
CREATE TABLE IF NOT EXISTS wx_expert_authentication (
    wx_id INT PRIMARY KEY, -- 微信号
    -- 性别
    sex VARCHAR(10),
    -- 学历
    education VARCHAR(50),
    -- 职称
    job_title VARCHAR(50),
    -- 所属机构
    organization VARCHAR(50),
    -- 所属区域
    area VARCHAR(50),
    -- 专业方向
    specialty VARCHAR(50),
    -- 研究方向
    research_direction VARCHAR(50),
    -- 个人简介
    personal_introduction VARCHAR(2000),
    -- 认证状态
    authentication_status BOOLEAN DEFAULT false,
    -- 认证通过时间
    authentication_time TIMESTAMP,
    -- 信息提交时间
    submit_time TIMESTAMP DEFAULT now(),

    FOREIGN KEY (wx_id) REFERENCES wx_user(wx_id)

);'''

import datetime
import logging
import psycopg2


logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
class WxExpertAuthenticationDaoMethods: 

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn


    def get_wx_expert_authentication_by_wx_id(self, wx_id):
        
        sql = "SELECT * FROM wx_expert_authentication WHERE wx_id = %s"
        self.cursor.execute(sql, (wx_id,))
        result = self.cursor.fetchone()
        return result
    
    # 弃用
    # def insert_or_update_wx_expert_authentication(self, wx_id, sex, education, job_title, organization, area, specialty, research_direction, personal_introduction):
    #     if self.get_wx_expert_authentication_by_wx_id(wx_id):
    #         sql = "UPDATE wx_expert_authentication SET sex = %s, education = %s, job_title = %s, organization = %s, area = %s, specialty = %s, research_direction = %s, personal_introduction = %s WHERE wx_id = %s"
    #         self.cursor.execute(sql, (sex, education, job_title, organization, area, specialty, research_direction, personal_introduction, wx_id))
    #         self.conn.commit()
    #         logger.info("更新专家认证信息成功")
    #     else:
    #         sql = "INSERT INTO wx_expert_authentication (wx_id, sex, education, job_title, organization, area, specialty, research_direction, personal_introduction) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
    #         self.cursor.execute(sql, (wx_id, sex, education, job_title, organization, area, specialty, research_direction, personal_introduction))
    #         self.conn.commit()
    #         logger.info("插入专家认证信息成功")

    # wx_id, education, job_title, specialty, job, personal_introduction
    def insert_or_update_wx_expert_authentication(self, wx_id, education, job_title, specialty, job, personal_introduction):

        if self.get_wx_expert_authentication_by_wx_id(wx_id):
            sql = "UPDATE wx_expert_authentication SET education = %s, job_title = %s, specialty = %s, job = %s, personal_introduction = %s WHERE wx_id = %s"
            self.cursor.execute(sql, (education, job_title, specialty, job, personal_introduction, wx_id))
            self.conn.commit()
            logger.info("更新专家认证信息成功")
        else:
            sql = "INSERT INTO wx_expert_authentication (wx_id, education, job_title, specialty, job, personal_introduction) VALUES (%s, %s, %s, %s, %s, %s)"
            self.cursor.execute(sql, (wx_id, education, job_title, specialty, job, personal_introduction))
            self.conn.commit()
            logger.info("插入专家认证信息成功")



    def update_authentication_status(self, wx_id, authentication_status):
        sql = "UPDATE wx_expert_authentication SET authentication_status = %s, authentication_time = %s WHERE wx_id = %s"
        self.cursor.execute(sql, (authentication_status, datetime.datetime.now(), wx_id))
        self.conn.commit()
        logger.info("更新专家认证状态成功")


    # 获取所有未认证的专家
    def get_all_un_authentication_expert(self, page=1, limit=10):
        sql = "SELECT * FROM wx_expert_authentication WHERE authentication_status = false ORDER BY submit_time DESC LIMIT %s OFFSET %s"
        self.cursor.execute(sql, (limit, (page-1)*limit))
        result = self.cursor.fetchall()
        return result
    
    # 获取所有未认证的专家数量
    def get_all_un_authentication_expert_count(self):
        sql = "SELECT COUNT(*) FROM wx_expert_authentication WHERE authentication_status = false"
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        return result["count"]  # 修改这行返回结果

    
    # 获取所有已认证的专家
    def get_all_authentication_expert(self, page=1, limit=10):
        sql = "SELECT * FROM wx_expert_authentication WHERE authentication_status = true ORDER BY authentication_time DESC LIMIT %s OFFSET %s"
        self.cursor.execute(sql, (limit, (page-1)*limit))
        result = self.cursor.fetchall()
        return result
    
    def get_all_authentication_expert_count(self):
        sql = "SELECT COUNT(*) FROM wx_expert_authentication WHERE authentication_status = true"
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        return result["count"]
    
    def get_all_expert(self, page=1, limit=10):
        sql = "SELECT * FROM wx_expert_authentication ORDER BY submit_time DESC LIMIT %s OFFSET %s"
        self.cursor.execute(sql, (limit, (page-1)*limit))
        result = self.cursor.fetchall()
        return result
    
    def get_all_expert_count(self):
        sql = "SELECT COUNT(*) FROM wx_expert_authentication"
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        return result["count"]
        